package com.wy.business.market.pda.services;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.wy.business.market.pda.pojos.UsersRT;
import com.wy.framework.constants.RtnCodeConstant;
import com.wy.framework.dao.ErrorSqlException;
import com.wy.framework.services.AbstractBaseService;
import com.wy.component.communication.RmtShellExecutor;
import com.wy.component.dataConvert.DateUtil;
import com.wy.component.dataConvert.StringUtil;

/**
 * 项目名称：openportal
 * 类名称：ProductManageService
 * 类描述：商品管理服务层
 * 创建人：张旭星
 * 创建时间：2016-2-14 下午3:39:35
 * 修改人：张旭星
 * 修改时间：2016-2-14 下午3:39:35
 * 版本信息：@version 1.0
 * Copyright (c) 2016万雍科技（上海）有限公司-版权所有  
 * 修改备注：
 * @version 
 */
@Service
@SuppressWarnings({"rawtypes","unchecked"})
public class ProductManageService extends AbstractBaseService {

	private static Logger logger = LoggerFactory
			.getLogger(ProductManageService.class);

	public List<Map> getProductInfo(String barcode, UsersRT usersRT) throws ErrorSqlException {
		List params = new ArrayList();
		params.add(barcode);
		params.add(barcode);
		logger.info("根据条码:"+barcode+"取得商品信息..");
		List<Map> list = new ArrayList();
		String appendSql = "";
		//可查所有商品
		if(StringUtils.equals(usersRT.getType(), "SM") || StringUtils.equals(usersRT.getUsr(), "OPS$ED01")){
			list = dataBaseDao.queryForListAuchan("items.get", params, getJtByIpSegment(usersRT.getIpSegment()));
		}
		//当前处的商品
		else if(StringUtils.equals(usersRT.getType(), "MG")){
			appendSql = " and s.division_no='"+usersRT.getDivisionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.get", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		//当前部的商品
		else{
			appendSql = " and s.section_no='"+usersRT.getSectionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.get", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
//		List<Map> list = dataBaseDao.queryForList("items.get", params);
		if(list!=null && list.size()>0){
			Map map = list.get(0);
			logger.info("根据条码:"+barcode+"取得将来销价..");
			List<Map> list1 = dataBaseDao.queryForListAuchan("items.local_sell_price", params, getJtByIpSegment(usersRT.getIpSegment()));
//			List<Map> list1 = dataBaseDao.queryForList("items.local_sell_price", params);
			Double LOCAL_SELL_PRICE = null;
			if(list1!=null && list1.size()>0){
				LOCAL_SELL_PRICE = (Double) list1.get(0).get("LOCAL_SELL_PRICE");
				map.put("LOCAL_SELL_PRICE", LOCAL_SELL_PRICE==null?0:LOCAL_SELL_PRICE);
			}else{
				map.put("LOCAL_SELL_PRICE", 0);
			}
			//map.put("NET_MARGIN_RATE", -2);
		}
		return list;
	}

	public List<Map> getSellInfo(String barcode, UsersRT usersRT) throws ErrorSqlException {
		List params = new ArrayList();
		params.add(barcode);
		params.add(barcode);
		logger.info("根据条码:"+barcode+"取得销售信息..");
		
		List<Map> list = new ArrayList();
		String appendSql = "";
		//可查所有商品
		if(StringUtils.equals(usersRT.getType(), "SM") || StringUtils.equals(usersRT.getUsr(), "OPS$ED01")){
			list = dataBaseDao.queryForListAuchan("items.getSellInfo", params, getJtByIpSegment(usersRT.getIpSegment()));
		}
		//当前处的商品
		else if(StringUtils.equals(usersRT.getType(), "MG")){
			appendSql = " and s.division_no='"+usersRT.getDivisionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getSellInfo", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		//当前部的商品
		else{
			appendSql = " and s.section_no='"+usersRT.getSectionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getSellInfo", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		
//		List<Map> list = dataBaseDao.queryForList("items.getSellInfo", params);
		if(list!=null && list.size()>0){
			Map map = list.get(0);
//			map.put("NET_MARGIN_RATE", -2);
//			map.put("MARGIN_RATE", -2);
			logger.info("根据条码:"+barcode+"取得前7天销量..");
			List<Map> list1 = dataBaseDao.queryForListAuchan("items.last7sellInfo", params, getJtByIpSegment(usersRT.getIpSegment()));
//			List<Map> list1 = dataBaseDao.queryForList("items.last7sellInfo", params);
			List resultList = new ArrayList();
			//构造前7天月日集合
			for(int i=0;i<7;i++){
				Date tempDate = DateUtil.getDiffDate(new Date(), -(i+1));
				String md = DateUtil.formatDateStr(tempDate, "MM/dd");
//				if(list1!=null && list1.size()>0){
					boolean exists = false;
					for(Map m:list1){
						if(StringUtils.equals(md, m.get("SALES_DATE_MD").toString())){
							resultList.add(m);
							exists = true;
						}
					}
					if(!exists){
						Map m = new HashMap();
						m.put("SALES", 0);
						m.put("SALES_AMNT", 0);
						m.put("MARGIN_RATE", 0);
						m.put("SALES_DATE_CN", DateUtil.formatDateStr(tempDate, "E"));
						m.put("SALES_DATE_EN", DateUtil.formatDateStrEn(tempDate, "E"));
						m.put("SALES_DATE_MD", md);
						resultList.add(m);
					}
//				}
			}
			map.put("last7sellInfo", resultList);
		}
		return list;
	}

	public List<Map> getStockInfo(String barcode, UsersRT usersRT) throws ErrorSqlException {
		List params = new ArrayList();
		params.add(barcode);
		params.add(barcode);
		logger.info("根据条码:"+barcode+"取得库存信息..");
		
		List<Map> list = new ArrayList();
		String appendSql = "";
		//可查所有商品
		if(StringUtils.equals(usersRT.getType(), "SM") || StringUtils.equals(usersRT.getUsr(), "OPS$ED01")){
			list = dataBaseDao.queryForListAuchan("items.getStockInfo", params, getJtByIpSegment(usersRT.getIpSegment()));
		}
		//当前处的商品
		else if(StringUtils.equals(usersRT.getType(), "MG")){
			appendSql = " and s.division_no='"+usersRT.getDivisionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getStockInfo", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		//当前部的商品
		else{
			appendSql = " and s.section_no='"+usersRT.getSectionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getStockInfo", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		
//		List<Map> list = dataBaseDao.queryForList("items.getStockInfo", params);
		return list;
	}

	public List<Map> getShelfCapacity(String barcode, UsersRT usersRT) throws ErrorSqlException {
		List params = new ArrayList();
		params.add(barcode);
		params.add(barcode);
		logger.info("根据条码:"+barcode+"取得排面量信息..");
		
		List<Map> list = new ArrayList();
		String appendSql = "";
		//可查所有商品
		if(StringUtils.equals(usersRT.getType(), "SM") || StringUtils.equals(usersRT.getUsr(), "OPS$ED01")){
			list = dataBaseDao.queryForListAuchan("items.getShelfCapacity", params, getJtByIpSegment(usersRT.getIpSegment()));
		}
		//当前处的商品
		else if(StringUtils.equals(usersRT.getType(), "MG")){
			appendSql = " and s.division_no='"+usersRT.getDivisionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getShelfCapacity", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		//当前部的商品
		else{
			appendSql = " and s.section_no='"+usersRT.getSectionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getShelfCapacity", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		
//		List<Map> list = dataBaseDao.queryForList("items.getShelfCapacity", params);
		return list;
	}

	public List<Map> getLabelInfo(String barcode, UsersRT usersRT) throws ErrorSqlException {
		List params = new ArrayList();
		params.add(barcode);
		params.add(barcode);
		logger.info("根据条码:"+barcode+"取得标签打印信息..");
		
		List<Map> list = new ArrayList();
		String appendSql = "";
		//可查所有商品
		if(StringUtils.equals(usersRT.getType(), "SM") || StringUtils.equals(usersRT.getUsr(), "OPS$ED01")){
			list = dataBaseDao.queryForListAuchan("items.getLabelInfo", params, getJtByIpSegment(usersRT.getIpSegment()));
		}
		//当前处的商品
		else if(StringUtils.equals(usersRT.getType(), "MG")){
			appendSql = " and s.division_no='"+usersRT.getDivisionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getLabelInfo", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		//当前部的商品
		else{
			appendSql = " and s.section_no='"+usersRT.getSectionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getLabelInfo", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		
//		List<Map> list = dataBaseDao.queryForList("items.getLabelInfo", params);
		return list;
	}

	public List<Map> getChangePriceInfo(String barcode, UsersRT usersRT) throws ErrorSqlException {
		List params = new ArrayList();
		params.add(barcode);
		params.add(barcode);
		logger.info("根据条码:"+barcode+"取得标签打印信息..");
		
		List<Map> list = new ArrayList();
		String appendSql = "";
		//可查所有商品
		if(StringUtils.equals(usersRT.getType(), "SM") || StringUtils.equals(usersRT.getUsr(), "OPS$ED01")){
			list = dataBaseDao.queryForListAuchan("items.getChangePriceInfo", params, getJtByIpSegment(usersRT.getIpSegment()));
		}
		//当前处的商品
		else if(StringUtils.equals(usersRT.getType(), "MG")){
			appendSql = " and s.division_no='"+usersRT.getDivisionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getChangePriceInfo", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		//当前部的商品
		else{
			appendSql = " and s.section_no='"+usersRT.getSectionNo()+"' ";
			list = dataBaseDao.queryForListAuchan("items.getChangePriceInfo", params, getJtByIpSegment(usersRT.getIpSegment()), appendSql);
		}
		
//		List<Map> list = dataBaseDao.queryForList("items.getChangePriceInfo", params);
		return list;
	}

	public String calMargin(String itemNo, String price, UsersRT usersRT, String i18n) throws SQLException, ErrorSqlException {
		/*logger.info("取得预估毛利率..");
		CallableStatement stat = null;
		Connection conn = null;
		String res = "";
		i18n = StringUtils.equals(i18n, "cn")?"CH":"EN";
		try {
//			conn = getJtByIpSegment("137.200.32.*").getDataSource().getConnection();
			conn = getJtByIpSegment(usersRT.getIpSegment()).getDataSource().getConnection();
			stat = conn.prepareCall("{? = call fun_app_cal_margin(?, ?, ?) }");
			stat.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR);
			stat.setString(2, itemNo); 
			stat.setString(3, price); 
			stat.setString(4, i18n); 
			System.out.println(stat.executeUpdate());
		} catch (SQLException e) {
			res = e.toString();
			logger.error("存储过程出错:"+res);
		}finally{
			if (stat != null)
				stat.close();
			if (conn != null)
				conn.close();
		}
		if(StringUtils.contains(res, "SQLException")){
			res = res.substring(res.indexOf(":",30)+2,res.indexOf("\n")) ;
		}else {
			res = RtnCodeConstant.RTN_CODE_SUCCESS;
		}
		return res;*/
		
		logger.info("取得预估毛利率..");
		i18n = StringUtils.equals(i18n, "cn")?"CH":"EN";
		List<Map<String, Object>> list = getJtByIpSegment(usersRT.getIpSegment()).queryForList("select fun_app_cal_margin('"+itemNo+"',"+price+",'"+i18n+"') rate from dual");
//			List<Map<String, Object>> list = dataBaseDao.queryNoKeyForList("select fun_app_cal_margin('"+itemNo+"',"+price+") rate from dual",null);
		return (String) ((Map)list.get(0)).get("RATE");
	}

	public String changeItemPrice(String itemNo, String price, String changeBeginDate, UsersRT usersRT, String i18n, String reason) throws SQLException {
		CallableStatement stat = null;
		Connection conn = null;
		String res = "";
		try {
			java.sql.Date sqlDate = new java.sql.Date(DateUtil.convertToDate(StringUtils.replace(changeBeginDate, "-", ""), "yyyyMMdd").getTime());
			conn = getJtByIpSegment(usersRT.getIpSegment()).getDataSource().getConnection();
//			conn = dataBaseDao.getJdbcTemplate().getDataSource().getConnection();
			stat = conn.prepareCall("{ Call PROC_APP_ITEM_PRICE_CHG(?,?,?,?,?,?) }");
			stat.setString(1, usersRT.getUsr()); 
//			stat.setString(1, "OPS$T027"); 
			stat.setString(2, itemNo); 
			stat.setDate(3, sqlDate);
			stat.setDouble(4, Double.parseDouble(price)); 
			stat.setString(5, StringUtils.equals(i18n, "cn")?"CH":"EN"); 
			stat.setInt(6, Integer.parseInt(reason)); 
			res =stat.execute()+"";
		} catch (SQLException e) {
			res = e.toString();
			logger.error("存储过程出错:"+res);
		}finally{
			if (stat != null)
				stat.close();
			if (conn != null)
				conn.close();
		}
		if(StringUtils.contains(res, "SQLException")){
			res = res.substring(res.indexOf(":",30)+2,res.indexOf("\n")) ;
		}else if(StringUtils.equals(res, "false")){
			res = RtnCodeConstant.RTN_CODE_SUCCESS;
		}
		return res;
	}

	public String changeShelfCapacity(String itemNo, String linearCapacity,
			String layer, String firstLinearCapacity, String deep, UsersRT usersRT, String i18n) throws SQLException {
		CallableStatement stat = null;
		Connection conn = null;
		String res = "";
		try {
			conn = getJtByIpSegment(usersRT.getIpSegment()).getDataSource().getConnection();
//			conn = dataBaseDao.getJdbcTemplate().getDataSource().getConnection();
			stat = conn.prepareCall("{ Call PROC_APP_ITEM_XYZ_CHG(?,?,?,?,?,?,?) }");
			stat.setString(1, usersRT.getUsr()); 
//			stat.setString(1, "OPS$T027"); 
			stat.setString(2, itemNo); 
			stat.setInt(3, Integer.parseInt(linearCapacity)); 
			stat.setInt(4, Integer.parseInt(layer)); 
			stat.setInt(5, Integer.parseInt(firstLinearCapacity)); 
			stat.setInt(6, Integer.parseInt(deep)); 
			stat.setString(7, StringUtils.equals(i18n, "cn")?"CH":"EN"); 
			res =stat.execute()+"";
		} catch (SQLException e) {
			res = e.toString();
			logger.error("存储过程出错:"+res);
		}finally{
			if (stat != null)
				stat.close();
			if (conn != null)
				conn.close();
		}
		if(StringUtils.contains(res, "SQLException")){
			res = res.substring(res.indexOf(":",30)+2,res.indexOf("\n")) ;
		}else if(StringUtils.equals(res, "false")){
			res = RtnCodeConstant.RTN_CODE_SUCCESS;
		}
		return res;
	}

	public String printLabel(String itemNo, String groupName, String railcard,
			String printQty, UsersRT usersRT, String i18n) throws ErrorSqlException, SQLException {
		CallableStatement stat = null;
		Connection conn = null;
		String res = "";
		i18n = StringUtils.equals(i18n, "cn")?"CH":"EN";
		try {
//			conn = getJtByIpSegment("137.200.32.*").getDataSource().getConnection();
			conn = getJtByIpSegment(usersRT.getIpSegment()).getDataSource().getConnection();
			stat = conn.prepareCall("{? = call FUN_APP_PRINT_LABEL(?, ?, ?,?,?) }");
			stat.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR);
			stat.setString(2, itemNo); 
			stat.setString(3, groupName); 
			stat.setString(4, railcard); 
			stat.setString(5, printQty); 
			stat.setString(6, i18n); 
			stat.executeUpdate();
		} catch (SQLException e) {
			res = e.toString();
			logger.error("存储过程出错:"+res);
		}finally{
			if (stat != null)
				stat.close();
			if (conn != null)
				conn.close();
		}
		if(StringUtils.contains(res, "SQLException")){
			res = res.substring(res.indexOf(":",30)+2,res.indexOf("\n")) ;
		}else{
			i18n = StringUtils.equals(i18n, "cn")?"CH":"EN";
			List<Map<String, Object>> list = getJtByIpSegment(usersRT.getIpSegment()).queryForList("select FUN_APP_PRINT_LABEL('"+itemNo+"','"+groupName+"','"+railcard+"','"+printQty+"','"+i18n+"') rate from dual");
//			List<Map<String, Object>> list = getJtByIpSegment("137.200.32.*").queryForList("select FUN_APP_PRINT_LABEL('"+itemNo+"','"+groupName+"','"+railcard+"','"+printQty+"','"+i18n+"') rate from dual");
			String shell = (String) ((Map)list.get(0)).get("RATE");
			
	        String usr = usersRT.getSshUsr();
	        String password = usersRT.getPassword();
	        String serverIP = usersRT.getRtIp();
	        System.out.println(usr+";"+password+";"+serverIP);
//	        String usr = "t012";
//	        String password = "t012";
//	        String serverIP = "137.200.32.209";
	        RmtShellExecutor exe = new RmtShellExecutor(serverIP, usr, password);
	        String outInf;
	        try {
	            outInf = exe.exec(shell);
	            System.out.println("outException= " + outInf);
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
			res = RtnCodeConstant.RTN_CODE_SUCCESS;
		}
		return res;
	}

	public String uploadOutStock(String barcodes, UsersRT usersRT, String i18n) throws SQLException {
		CallableStatement stat = null;
		Connection conn = null;
		String res = "";
		i18n = StringUtils.equals(i18n, "cn")?"CH":"EN";
		try {
			conn = getJtByIpSegment(usersRT.getIpSegment()).getDataSource().getConnection();
//			conn = dataBaseDao.getJdbcTemplate().getDataSource().getConnection();
			String[] arr = barcodes.split(",");
			for(int i=0;i<arr.length;i++){
				if(StringUtils.isEmpty(arr[i]))
					continue;
				stat = conn.prepareCall("{ Call PROC_APP_ITEM_OOS(?, ?, ?) }");
				stat.setString(1, usersRT.getUsr()); 
//				stat.setString(1, "OPS$T027"); 
				stat.setString(2, arr[i]); 
				stat.setString(3, i18n); 
				res =stat.execute()+"";
			}
		} catch (SQLException e) {
			res = e.toString();
			logger.error("存储过程出错:"+res);
		}finally{
			if (stat != null)
				stat.close();
			if (conn != null)
				conn.close();
		}
		if(StringUtils.contains(res, "SQLException")){
			res = res.substring(res.indexOf(":",30)+2,res.indexOf("\n")) ;
		}else if(StringUtils.equals(res, "false")){
			res = RtnCodeConstant.RTN_CODE_SUCCESS;
		}
		return res;
	}
	
	public String uploadOutStockDel(String barcodes, UsersRT usersRT, String i18n) throws SQLException {
		CallableStatement stat = null;
		Connection conn = null;
		String res = "";
		i18n = StringUtils.equals(i18n, "cn")?"CH":"EN";
		try {
			conn = getJtByIpSegment(usersRT.getIpSegment()).getDataSource().getConnection();
//			conn = dataBaseDao.getJdbcTemplate().getDataSource().getConnection();
			String[] arr = barcodes.split(",");
			for(int i=0;i<arr.length;i++){
				if(StringUtils.isEmpty(arr[i]))
					continue;
				stat = conn.prepareCall("{ Call PROC_APP_ITEM_OOS_DELETE(?, ?, ?) }");
				stat.setString(1, usersRT.getUsr()); 
//				stat.setString(1, "OPS$T027"); 
				stat.setString(2, arr[i]); 
				stat.setString(3, i18n); 
				res =stat.execute()+"";
			}
		} catch (SQLException e) {
			res = e.toString();
			logger.error("存储过程出错:"+res);
		}finally{
			if (stat != null)
				stat.close();
			if (conn != null)
				conn.close();
		}
		if(StringUtils.contains(res, "SQLException")){
			res = res.substring(res.indexOf(":",30)+2,res.indexOf("\n")) ;
		}else if(StringUtils.equals(res, "false")){
			res = RtnCodeConstant.RTN_CODE_SUCCESS;
		}
		return res;
	}

	public String judgeBarcode(String barcodes, UsersRT loginAuchanUser) throws ErrorSqlException {
		List<Map<String, Object>> list = getJtByIpSegment(loginAuchanUser.getIpSegment()).queryForList("select barcode from barcode b where b.barcode in " + "("+StringUtil.concatStrForSql(barcodes)+")");
		String result = "";
		if(list!=null && list.size()>0){
			for(Map m:list){
				result += "," + m.get("BARCODE");
			}
			result = result.substring(1);
		}
		return result;
	}
	
	public String judgeItemNo(String itemNo, UsersRT loginAuchanUser) throws ErrorSqlException {
		List<Map<String, Object>> list = getJtByIpSegment(loginAuchanUser.getIpSegment()).queryForList("select barcode from barcode b where b.item_no ='"+itemNo+"' or b.barcode='"+itemNo+"' ");
		String result = "";
		if(list!=null && list.size()>0){
			for(Map m:list){
				result += "," + m.get("BARCODE");
			}
			result = result.substring(1);
		}
		return result;
	}
	
}
